﻿'Description:批次检验类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Yuxj
'create Date:2017-10-31
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Data.OracleClient
Public Class uLMSdealFaultBillBusiness
    Implements IBusiness.uLMSDealFaultBillIbusiness
    ''' <summary>
    ''' add by yuxj 20171123 通过不合格审理单获取信息
    ''' </summary>
    ''' <param name="strBHGSL"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetBHGSLInfo(ByVal strBHGSL As String) As DataTable Implements uLMSDealFaultBillIbusiness.GetBHGSLInfo
        Dim sb As New StringBuilder
        sb.AppendFormat("SELECT non.CONTAINERNONCONFORMINGINFOID, non.zbhgdh,pf.productfamilyname,D.DealFaultBILLNAME,d.DealFaultbillid,non.containerid FROM containernonconforminginfo non ")
        sb.AppendFormat("LEFT JOIN DealFaultBILL D ON D.CONTAINERNONCONFORMINGINFOID = NON.CONTAINERNONCONFORMINGINFOID ")
        sb.AppendFormat("LEFT JOIN container c ON non.containerid=c.containerid ")
        sb.AppendFormat("LEFT JOIN product p ON c.productid=p.productid ")
        sb.AppendFormat("LEFT JOIN productfamily pf ON p.productfamilyid=pf.productfamilyid ")
        sb.AppendFormat("WHERE non.zbhgdh='{0}' ", strBHGSL)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    Private Function CheckPGDExists(ByVal strBillName As String) As Boolean Implements uLMSDealFaultBillIbusiness.CheckPGDExists
        CheckPGDExists = False
        Dim sb As New StringBuilder
        sb.AppendFormat("SELECT d.DealFaultbillname FROM DealFaultbill d where  d.DealFaultbillname='{0}'", strBillName)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            Return True
        End If
    End Function

    ''' <summary>
    ''' 获取排故单流水号
    ''' Modify by Yuxj20190617 流水号四位改为三位 update:wangjh 20191104 不只是三位，更改为无限大
    ''' </summary>
    ''' <param name="strBillNameFix"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetPGDNextFlowSN(ByVal strBillNameFix As String) As String Implements uLMSDealFaultBillIbusiness.GetPGDNextFlowSN
        'PGD2017110001
        Dim sb As New StringBuilder
        sb.AppendFormat("SELECT max(to_number(replace(d.DealFaultbillname,'{0}',''))) DealFaultbillname FROM DealFaultbill d where isuse=0 and d.DealFaultbillname like '{0}%'", strBillNameFix)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            Dim currentName As String = dt.Rows(0)("DealFaultbillname").ToString
            If currentName = String.Empty Then
                GetPGDNextFlowSN = "0001"
            Else
                ' GetPGDNextFlowSN = (Convert.ToInt32(currentName.Substring(currentName.Length - 3, 3)) + 1).ToString().PadLeft(3, "0")
                GetPGDNextFlowSN = Integer.Parse(currentName.Split("-").Last) + 1
                If GetPGDNextFlowSN < 1000 Then
                    GetPGDNextFlowSN = GetPGDNextFlowSN.ToString.PadLeft(4, "0")
                End If
            End If
        Else
            GetPGDNextFlowSN = "0001"
        End If
    End Function
    Private Function GetBHGDH(ByVal strBillID As String) As String Implements uLMSDealFaultBillIbusiness.GetBHGDH
        Dim strsql As String = String.Format(" SELECT non.zbhgdh FROM dealfaultbill d  ")
        strsql += " LEFT JOIN containernonconforminginfo non ON d.containernonconforminginfoid=non.containernonconforminginfoid "
        strsql += String.Format("WHERE d.dealfaultbillid='{0}'", strBillID)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        If dt.Rows.Count = 0 Then
            Return ""
        Else
            Return dt.Rows(0)("zbhgdh").ToString
        End If
    End Function

    ''' <summary>
    ''' 获取排故单相关信息 modify YangJiang 20180524 string/string
    ''' 添加多个件号时从排故单明细查询
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetPGDInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSDealFaultBillIbusiness.GetPGDInfo
        GetPGDInfo = New uMESProcessResult
        'Modify by YangJiang 20180611 pf.PRODUCTFAMILYNAME->d.enginefamily,ut.attributevalue->d.engineno
        Dim strsql As String =
            <string>
                 SELECT MFG.MFGORDERNAME,OT.ORDERTYPENAME,d.enginefamily PRODUCTFAMILYNAME,C.TogetherFlag,d.engineno Machine, pb.productname MATNR,p.description,C.CONTAINERNAME, 
                    --DECODE(D.TRANSFERSTATUS,'0','待转出', '1', '已转出','2', '分厂已接收', '3', '分厂已返回', '4', '已完成', D.TRANSFERSTATUS) 
                    --DECODE(D.TRANSFERSTATUS,'0','待审核', '1', '待确认','2', '未返出', '3', '未接收', '4', '未完成','5', '未返回','6', '未领取','7', '已领取', D.TRANSFERSTATUS) transferstatus,
                    CASE WHEN D.TRANSFERSTATUS='8' THEN DECODE(TR.faultcheckreportno,NULL,'待检验故检报告','待工程部修理文件') else 
                    DECODE(D.TRANSFERSTATUS,  '0','待返出单位技术审核',   '1','待返出单位检验确认',  '2', '待返出单位返出', '3', '待接收单位接收',  '4', 
                    '待接收单位完成', '5', DECODE(TR.faultcheckreportno,NULL,'待接收单位返回','待接收单位修理'),'6', '待返出单位领取', '7', '现场已领取',D.TRANSFERSTATUS) END transferstatus,
                    D.REPAIRCOUNT,D.DEALFAULTBILLNAME,D.DEALFAULTBILLID,TR.PAGECOUNT, GETFACTORYNAME_FUN(P.Factoryid) SendFactory,D.Transferstatus status,
                    getEmpFullName(TR.FROMEMPID) FromEmp, TR.FROMTIME, TR.REQUIREFINISHTIME,GETFACTORYNAME_FUN(TR.RECVFACTORYID) RecvFactory,TR.RECVFACTORYID, 
                    t1.teamname RecvFactoryTeam, getEmpFullName(TR.RECVFACTORYEMPID) RecvEmp,TR.RECVFACTORYQTY, TR.RECVFACTORYTIME,tr.recvfactoryteamid,
                    TR.RECVFACTORYPASSQTY,TR.RECVFACTORYFAILQTY,getEmpFullName(TR.RECVFACTORYRETURNEMPID) FactoryReturnEmp,getEmpFullName(TR.ACCEPTEMPID) AcceptEmp,
                    TR.RECVFACTORYRETURNTIME,t2.teamname AcceptTeam,getEmpFullName(TR.DISPATCHACCEPTEMPID) RecvFactoryReturnEmp,TR.ACCEPTTIME,
                    D.Machineworkhours,D.Faultdesc,D.Dealfaulttype,GETFACTORYNAME_FUN(D.Returnfactoryid) ReturnFactory,cno.zbhgdh,tr.fromqty,
                    getEmpFullName(cno.checkemployeeid) checker,D.Returnfactoryid,cem.productbatchno ComProductName,tr.acceptteamid,decode(D.ISFAULTCHECK,'1','是','否') ISFAULTCHECKT
                    FROM DEALFAULTBILL D
                    LEFT JOIN DEALFAULTBILLTRANSFER TR ON TR.DEALFAULTBILLID = D.DEALFAULTBILLID
                    LEFT JOIN CONTAINER C ON D.CONTAINERID = C.CONTAINERID
                    LEFT JOIN PRODUCT P ON C.PRODUCTID = P.PRODUCTID
                    LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
                    LEFT JOIN MFGORDER MFG ON D.MFGORDERID = MFG.MFGORDERID
                    LEFT JOIN ORDERTYPE OT ON MFG.ORDERTYPEID = OT.ORDERTYPEID
                    --LEFT JOIN PRODUCTFAMILY PF ON P.PRODUCTFAMILYID = PF.PRODUCTFAMILYID
                    LEFT JOIN EMPLOYEE E ON D.CREATEEMPLOYEEID = E.EMPLOYEEID
                    --LEFT JOIN Userattribute ut ON ut.parentid=c.containerid AND ut.userattributename='发动机'
                    LEFT JOIN team t1 ON t1.teamid=tr.recvfactoryteamid
                    LEFT JOIN team t2 ON t2.teamid=tr.acceptteamid
                    LEFT JOIN containernonconforminginfo cno ON cno.containernonconforminginfoid=d.containernonconforminginfoid
                    LEFT JOIN (SELECT dd.dealfaultbillid,MAX(dd.issueerpmaterialid) issueerpmaterialid FROM  
                                dealfaultbilldetail dd GROUP BY dd.dealfaultbillid) dd
                              ON dd.dealfaultbillid=d.dealfaultbillid
                    LEFT JOIN issueerpmaterial iem ON IEM.ISSUEERPMATERIALID = nvl(D.ISSUEERPMATERIALID,dd.issueerpmaterialid)
                    LEFT JOIN collecterpmaterial cem ON cem.collecterpmaterialid=iem.collecterpmaterialid
                 WHERE d.isuse='0'
            </string>
        If para.ContainsKey("DealFaultBillID") Then
            strsql += String.Format(" and d.DealFaultBillID='{0}'", para("DealFaultBillID"))
        End If
        'add YangJiang 20180524 发动机序列号
        If para.ContainsKey("Machine") Then
            strsql += String.Format(" AND d.engineno like'%{0}%'", para("Machine"))
        End If

        'add YangJiang 20180608 根据件号查询
        If para.ContainsKey("ProductName") Then
            strsql += String.Format(" AND pb.productname ='{0}'", para("ProductName"))
        End If

        'modify YangJiang 20180607 =->like
        If para.ContainsKey("DealFaultBILLNAME") Then
            strsql += String.Format(" and d.DealFaultBILLNAME like '%{0}%'", para("DealFaultBILLNAME"))
        End If

        'Add by Yuxj20181204
        If para.ContainsKey("BeginDate") Then
            strsql += " and TO_DATE(TO_CHAR(D.CREATEDATE,'YYYY/MM/DD'),'YYYY/MM/DD') "
            If para.ContainsKey("EndDate") Then
                strsql += String.Format(" between TO_DATE('{0}','YYYY/MM/DD' ) and TO_DATE( '{1}','YYYY/MM/DD' ) ", para("BeginDate"), para("EndDate"))
            Else
                strsql += String.Format(" = TO_DATE('{0}','YYYY/MM/DD' ) ", para("BeginDate"))
            End If
        End If

        'Modify by Yuxj20180801 添加任务单查询
        If para.ContainsKey("TogetherFlag") Then
            If para.ContainsKey("GJ") Then
                strsql += String.Format(" and c.mfgorderid in (select ep.mfgorderid from enginepartplan ep where ep.containerno='{0}') ", para("TogetherFlag"))
            Else
                strsql += String.Format(" and c.TogetherFlag like '%{0}%'", para("TogetherFlag"))
            End If

        End If

        If para.ContainsKey("Status") Then
            'If para("Status") = "0" Then
            strsql += String.Format(" and d.TRANSFERSTATUS='{0}'", para("Status"))
            'Else
            '    strsql += String.Format(" and d.TRANSFERSTATUS>='{0}'", para("Status"))
            'End If
        End If
        'add YangJiang 20180528
        strsql += "  GROUP BY(tr.acceptteamid,MFG.MFGORDERNAME,OT.ORDERTYPENAME,d.enginefamily,C.TogetherFlag,d.engineno , pb.productname ,p.description,C.CONTAINERNAME, " &
                    "CASE WHEN D.TRANSFERSTATUS='8' THEN DECODE(TR.faultcheckreportno,NULL,'待检验故检报告','待工程部修理文件') else " &
                    "DECODE(D.TRANSFERSTATUS,  '0','待返出单位技术审核',   '1','待返出单位检验确认',  '2', '待返出单位返出', '3', '待接收单位接收',  '4', " &
        "'待接收单位完成', '5', DECODE(TR.faultcheckreportno,NULL,'待接收单位返回','待接收单位修理'),'6', '待返出单位领取', '7', '现场已领取',D.TRANSFERSTATUS) END ,D.Transferstatus ," &
                   " D.REPAIRCOUNT,D.DEALFAULTBILLNAME,D.DEALFAULTBILLID,TR.PAGECOUNT, GETFACTORYNAME_FUN(P.Factoryid) ," &
                  "  getEmpFullName(TR.FROMEMPID) , TR.FROMTIME, TR.REQUIREFINISHTIME,GETFACTORYNAME_FUN(TR.RECVFACTORYID) , TR.RECVFACTORYID,tr.recvfactoryteamid," &
                   " t1.teamname , getEmpFullName(TR.RECVFACTORYEMPID) ,TR.RECVFACTORYQTY, TR.RECVFACTORYTIME," &
                   " TR.RECVFACTORYPASSQTY,TR.RECVFACTORYFAILQTY,getEmpFullName(TR.RECVFACTORYRETURNEMPID) ,getEmpFullName(TR.ACCEPTEMPID)," &
                   " TR.RECVFACTORYRETURNTIME,t2.teamname ,getEmpFullName(TR.DISPATCHACCEPTEMPID) ,TR.ACCEPTTIME," &
                   " D.Machineworkhours,D.Faultdesc,D.Dealfaulttype,GETFACTORYNAME_FUN(D.Returnfactoryid) ,cno.zbhgdh,tr.fromqty," &
                   " getEmpFullName(cno.checkemployeeid) ,D.Returnfactoryid,cem.productbatchno,D.ISFAULTCHECK)"

        strsql += " order by d.DealFaultbillid  asc"

        If Not para.ContainsKey("PageSize") Then
            para("PageSize") = "30"
        End If

        If Not para.ContainsKey("CurrentPageIndex") Then
            para("CurrentPageIndex") = "1"
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strsql
        GetPGDInfo = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    Private Function GetPGDStepInfo(ByVal strPGDID As String) As DataTable Implements uLMSDealFaultBillIbusiness.GetPGDStepInfo
        Dim strsql As String =
            String.Format(" select * from DEALFAULTSTEP S  where s.DEALFAULTBILLID='{0}'  order by DealFaultbillid, to_number(S.DealFaultSEQUENCE) asc", strPGDID)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        Return dt
    End Function

    Private Function GetUserID(ByVal strEmp As String) As String Implements uLMSDealFaultBillIbusiness.GetUserID
        Dim sb As New StringBuilder
        sb.AppendFormat("select employeeid from employee e where lower(e.employeename)='{0}'", strEmp.ToLower)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count > 0 Then
            Return dt.Rows(0)("employeeid").ToString
        Else
            Return ""
        End If
    End Function

    Private Function InsertDealFaultBill(ByVal entity As BusinessRule.DTO.dealFaultBillInfoDTO) As Boolean Implements uLMSDealFaultBillIbusiness.InsertDealFaultBill
        InsertDealFaultBill = False 'modify YangJiang 20180524 WorkflowStepID,StepInfoID,IssueERPMaterialID,Notes
        'add YangJiang 20180611 ,ENGINEFAMILY,ENGINENO,:ENGINEFAMILY,:ENGINENO
        Dim strsql As String =
            <string>
                INSERT INTO DealFaultBILL
                      (CONTAINERID,   CONTAINERNONCONFORMINGINFOID,   CREATEDATE,   CREATEEMPLOYEEID,   DealFaultBILLID,   DealFaultBILLNAME,
                       DealFaultTYPE,   FAULTDESC,   MACHINEWORKHOURS,   REPAIRCOUNT,   RETURNFACTORYID, transferstatus, istransfer, isuse,WorkflowStepID,StepInfoID,IssueERPMaterialID,Notes
                        ,ENGINEFAMILY,ENGINENO)
                    VALUES
                      (:CONTAINERID,   :CONTAINERNONCONFORMINGINFOID,   SYSDATE,   :CREATEEMPLOYEEID,   :DealFaultBILLID,   :DealFaultBILLNAME,
                       :DealFaultTYPE,   :FAULTDESC,   :MACHINEWORKHOURS,   0,   :RETURNFACTORYID,'0','0','0',:WorkflowStepID,:StepInfoID,:IssueERPMaterialID,:Notes
                        ,:ENGINEFAMILY,:ENGINENO)
            </string>
        Dim insertpar(14) As OracleParameter
        insertpar(0) = DatabaseHelper.NewParameter(":CONTAINERID", entity.CONTAINERID, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":CONTAINERNONCONFORMINGINFOID", entity.CONTAINERNONCONFORMINGINFOID, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":CREATEEMPLOYEEID", entity.CREATEEMPLOYEEID, OracleType.VarChar)
        insertpar(3) = DatabaseHelper.NewParameter(":DealFaultBILLID", entity.DealFaultBILLID, OracleType.VarChar)
        insertpar(4) = DatabaseHelper.NewParameter(":DealFaultBILLNAME", entity.DealFaultBILLNAME, OracleType.VarChar)
        insertpar(5) = DatabaseHelper.NewParameter(":DealFaultTYPE", entity.DealFaultTYPE, OracleType.VarChar)
        insertpar(6) = DatabaseHelper.NewParameter(":FAULTDESC", entity.FAULTDESC, OracleType.VarChar)
        insertpar(7) = DatabaseHelper.NewParameter(":MACHINEWORKHOURS", entity.MACHINEWORKHOURS, OracleType.VarChar)
        'insertpar(8) = DatabaseHelper.NewParameter(":REPAIRCOUNT", entity.REPAIRCOUNT, OracleType.VarChar)
        insertpar(8) = DatabaseHelper.NewParameter(":RETURNFACTORYID", entity.RETURNFACTORYID, OracleType.VarChar)
        'add YangJiang 20180524 添加工序ID 和工步ID
        insertpar(9) = DatabaseHelper.NewParameter(":WorkflowStepID", entity.WORKFLOWSTEPID, OracleType.VarChar)
        insertpar(10) = DatabaseHelper.NewParameter(":StepInfoID", entity.STEPINFOID, OracleType.VarChar)
        insertpar(11) = DatabaseHelper.NewParameter(":IssueERPMaterialID", entity.IssueERPMaterialID, OracleType.VarChar)
        insertpar(12) = DatabaseHelper.NewParameter(":Notes", entity.Notes, OracleType.VarChar) 'add YangJiang 20180605 添加备注信息
        'add YangJiang 20180611 添加型号和机号
        insertpar(13) = DatabaseHelper.NewParameter(":ENGINEFAMILY", entity.EngineFamily, OracleType.VarChar)
        insertpar(14) = DatabaseHelper.NewParameter(":ENGINENO", entity.EngineNo, OracleType.VarChar)
        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql, insertpar)
        If (rows = 0) Then
            Return False
        Else
            Return True
        End If
    End Function

    Private Function DeleteDealFaultStep(ByVal strDealFaultBillid As String) As Boolean Implements uLMSDealFaultBillIbusiness.DeleteDealFaultStep
        Dim sb As New StringBuilder
        sb.AppendFormat("DELETE DealFaultstep where DealFaultBILLid='{0}'", strDealFaultBillid)
        DatabaseHelper.ExecuteSql(sb.ToString)
        Return True
    End Function

    Private Function GetFactoryList() As DataTable Implements uLMSDealFaultBillIbusiness.GetFactoryList
        Dim sb As New StringBuilder
        sb.AppendFormat("SELECT f.factoryid,f.factoryname FROM Factory f where f.enterpriseid IS NOT NULL order by f.factoryname asc")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    'update YangJiang 20180527 ByVal FactoryID As String,where t.factoryid='" + FactoryID + "' 
    Private Function GetTeamList(ByVal FactoryID As String) As DataTable Implements uLMSDealFaultBillIbusiness.GetTeamList
        Dim sb As New StringBuilder
        sb.AppendFormat("SELECT t.teamid,t.teamname FROM team t where t.factoryid='" + FactoryID + "' order by t.teamname asc")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    Private Function GetDealFaultTypeList() As DataTable Implements uLMSDealFaultBillIbusiness.GetDealFaultTypeList
        Dim sb As New StringBuilder
        sb.AppendFormat("SELECT dt.dealfaulttypeid,dt.dealfaulttypename FROM dealfaulttype dt")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    Private Function InsertDealFaultStep(ByVal dtStep As DataTable) As Boolean Implements uLMSDealFaultBillIbusiness.InsertDealFaultStep
        Dim strsql As String = String.Empty
        DeleteDealFaultStep(dtStep.Rows(0)("DealFaultbillid").ToString)
        For i As Integer = 0 To dtStep.Rows.Count - 1
            strsql = String.Format("INSERT INTO DealFaultstep  (DealFaultbillid, DealFaultdesc, DealFaultsequence, memo) VALUES ")
            strsql += String.Format("('{0}','{1}','{2}',null) ", dtStep.Rows(i)("DealFaultbillid").ToString, dtStep.Rows(i)("DealFaultdesc").ToString, (i + 1).ToString)
            DatabaseHelper.ExecuteSql(strsql)
        Next
        Return True
    End Function

#Region "转进转出方法"
    '发出，接收，处理，返回
    ''' <summary>
    ''' 发出
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function InsertDealFaultTransfer(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSDealFaultBillIbusiness.InsertDealFaultTransfer
        Dim sb As New StringBuilder
        sb.AppendLine(" INSERT INTO  dealfaultbilltransfer tr (tr.dealfaultbillid,tr.pagecount,tr.requirefinishtime,tr.FromQty,tr.fromempid,tr.fromtime,recvfactoryid) values ")
        sb.AppendFormat("('{0}','{1}',to_date('{2}','yyyy-mm-dd'),'{3}','{4}',sysdate,'{5}')", para("DealFaultbillid"), para("PageCount"), para("RequireDate"), para("FromQty"), para("Userid"), para("recvfactoryid"))
        DatabaseHelper.ExecuteSql(sb.ToString)

        sb = New StringBuilder
        sb.AppendFormat("update dealfaultbill set transferstatus='1' where dealfaultbillid='{0}' and transferstatus='0'", para("DealFaultbillid"))
        DatabaseHelper.ExecuteSql(sb.ToString)

        InsertDealFaultTransfer = True
    End Function

    '确认接收
    Private Function UpdateDealFaultTransferRecv(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSDealFaultBillIbusiness.UpdateDealFaultTransferRecv
        Dim sb As New StringBuilder
        sb.AppendLine(" update  dealfaultbilltransfer tr  set ")
        sb.AppendFormat(" RecvFactoryQty='{0}',RecvFactoryEmpID='{1}',RecvFactoryTeamID='{2}',RecvFactoryTime=sysdate ", para("RecvFactoryQty"), para("Userid"), para("RecvFactoryTeamID"))
        sb.AppendFormat(" where dealfaultbillid='{0}'", para("DealFaultbillid"))
        DatabaseHelper.ExecuteSql(sb.ToString)

        sb = New StringBuilder
        sb.AppendFormat("update dealfaultbill set transferstatus='2' where dealfaultbillid='{0}' and transferstatus='1'", para("DealFaultbillid"))
        DatabaseHelper.ExecuteSql(sb.ToString)

        UpdateDealFaultTransferRecv = True
    End Function

    '修完返回
    Private Function UpdateDealFaultTransferReturn(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSDealFaultBillIbusiness.UpdateDealFaultTransferReturn
        Dim sb As New StringBuilder
        sb.AppendLine(" update  dealfaultbilltransfer tr  set ")
        sb.AppendFormat(" recvfactorypassqty='{0}',recvfactoryfailqty='{1}',RecvFactoryReturnEmpID='{2}',RecvFactoryReturnTime=sysdate ", para("PassQty"), para("FailQty"), para("Userid"))
        sb.AppendFormat(" where dealfaultbillid='{0}'", para("DealFaultbillid"))
        DatabaseHelper.ExecuteSql(sb.ToString)

        sb = New StringBuilder
        sb.AppendFormat("update dealfaultbill set transferstatus='3' where dealfaultbillid='{0}' and transferstatus='2'", para("DealFaultbillid"))
        DatabaseHelper.ExecuteSql(sb.ToString)

        UpdateDealFaultTransferReturn = True
    End Function

    '返回确认
    Private Function UpdateDealFaultTransferAccept(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSDealFaultBillIbusiness.UpdateDealFaultTransferAccept
        Dim sb As New StringBuilder
        sb.AppendLine(" update  dealfaultbilltransfer tr  set ")
        sb.AppendFormat(" AcceptTeamID='{0}',DispatchAcceptEmpID='{1}',AcceptEmpID='{2}',AcceptTime=sysdate ", para("AcceptTeamID"), para("Userid"), para("AcceptEmpID"))
        sb.AppendFormat(" where dealfaultbillid='{0}'", para("DealFaultbillid"))
        DatabaseHelper.ExecuteSql(sb.ToString)

        sb = New StringBuilder
        sb.AppendFormat("update dealfaultbill set transferstatus='4' where dealfaultbillid='{0}' and transferstatus='3'", para("DealFaultbillid"))
        DatabaseHelper.ExecuteSql(sb.ToString)

        UpdateDealFaultTransferAccept = True
    End Function

    Private Function GetTeamEmpList(ByVal teamid As String) As DataTable Implements uLMSDealFaultBillIbusiness.GetTeamEmpList
        Dim sb As New StringBuilder
        sb.AppendFormat(" SELECT e.employeeid,e.employeename ||'/'||e.fullname employeename FROM employee e  ")
        sb.AppendFormat(" LEFT JOIN teamemployee te ON e.employeeid=te.employeeid  ")
        sb.AppendFormat(" LEFT JOIN team t ON t.teamid=te.teamid  ")
        sb.AppendFormat(" WHERE t.teamid='{0}'  ", teamid)
        sb.AppendFormat(" ORDER BY e.employeename ASC  ")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' 装试检验更新排故故障情况 add YangJiang 20180526
    ''' </summary>
    ''' <param name="faultdesc"></param>
    ''' <param name="dealfaultbillid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateDealFaultBill(ByVal faultdesc As String, ByVal dealfaultbillid As String) As Boolean Implements IBusiness.uLMSDealFaultBillIbusiness.UpdateDealFaultBill
        Dim strSQL = "UPDATE DEALFAULTBILL d SET d.faultdesc='" + faultdesc + "' WHERE d.dealfaultbillid='" + dealfaultbillid + "'"
        DatabaseHelper.ExecuteSql(strSQL)
        UpdateDealFaultBill = True
    End Function

    ''' <summary>
    ''' 装试调度更新排故类型和返分厂 add YangJiang20180526
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateFaultTypeAndFactory(ByVal para As System.Collections.Generic.Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSDealFaultBillIbusiness.UpdateFaultTypeAndFactory
        Dim strSQL As New StringBuilder
        strSQL.AppendLine("update dealfaultbill d set")
        strSQL.AppendFormat(" d.returnfactoryid='{0}',d.dealfaulttype='{1}' ", para("ReturnFactory"), para("DealFaultType"))
        strSQL.AppendFormat(" where d.dealfaultbillid='{0}'", para("DealFaultbillid"))
        DatabaseHelper.ExecuteSql(strSQL.ToString)
        UpdateFaultTypeAndFactory = True
    End Function

    ''' <summary>
    ''' 获取接收人和排故后接收人 add YangJiang 20180528
    ''' </summary>
    ''' <param name="FactoryID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetEmpList(ByVal FactoryID As String) As System.Data.DataTable Implements IBusiness.uLMSDealFaultBillIbusiness.GetEmpList
        Dim strSQL As New StringBuilder
        strSQL.AppendLine("SELECT e.employeeid,e.employeename ||'/'||e.fullname employeename FROM employee e")
        strSQL.AppendLine("LEFT JOIN SessionValues sv ON sv.sessionvaluesid=e.sessionvaluesid")
        strSQL.AppendFormat("WHERE sv.factoryid='{0}'", FactoryID)
        GetEmpList = DatabaseHelper.Query_ReturnDatTable(strSQL.ToString)
    End Function

    ''' <summary>
    ''' 撤销排故转出 add YangJiang 20180528
    ''' </summary>
    ''' <param name="DealFaultBillID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function Rework(ByVal DealFaultBillID As String) As Boolean Implements IBusiness.uLMSDealFaultBillIbusiness.Rework
        Dim sb As New StringBuilder
        sb.AppendFormat("update dealfaultbill set transferstatus='0' where dealfaultbillid='{0}' and transferstatus='1'", DealFaultBillID)
        DatabaseHelper.ExecuteSql(sb.ToString)

        Dim strSQL = "delete dealfaultbilltransfer tr where tr.dealfaultbillid='" + DealFaultBillID + "'"
        DatabaseHelper.ExecuteSql(strSQL)
        Rework = True
    End Function

    ''' <summary>
    ''' 一次申请多个部件批次排故时，将数据插入排故明细表 add YangJiang 20180719
    ''' </summary>
    ''' <param name="DealFaultBillID"></param>
    ''' <param name="IssueERPMaterialID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveDealFaultBillDetail(ByVal DealFaultBillID As String, ByVal IssueERPMaterialID As String) As Boolean Implements IBusiness.uLMSDealFaultBillIbusiness.SaveDealFaultBillDetail
        Dim strSQL As String = "INSERT INTO dealfaultbilldetail VALUES('" + DealFaultBillID + "','" + IssueERPMaterialID + "')"
        Dim i As Integer = DatabaseHelper.ExecuteSql(strSQL)
        If i > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' Add by Yuxj20180820
    ''' 查找排故单绑定的批次
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function SearchDealFaultContainer(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDealFaultBillIbusiness.SearchDealFaultContainer
        Dim strsql As String =
            <string>
                SELECT im.issueerpmaterialid,cm.productbatchno,cm.productname,nvl(dfc.containername,c.containername) containername,
                nvl(dfc.containerid,c.containerid) containerid,
                p.description productdesc,p.productrevision FROM dealfaultbill DF
                LEFT JOIN dealfaultbilldetail dfd ON df.Dealfaultbillid=dfd.dealfaultbillid
                LEFT JOIN issueerpmaterial im ON im.issueerpmaterialid=NVL(df.Issueerpmaterialid,dfd.issueerpmaterialid)
                LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid
                LEFT JOIN product p ON pm.productid=p.productid                
                LEFT JOIN COLLECTERPMATERIAL cm ON im.collecterpmaterialid=cm.collecterpmaterialid   
                LEFT JOIN container c ON c.containername=(cm.productname||'/'||cm.productbatchno)             
                LEFT JOIN 
                (SELECT dfc.dealfaultbillid,dfc.containerid,c.containername FROM dealfaultbillcontainer dfc 
                LEFT JOIN container c ON dfc.containerid=c.containerid ) dfc
                ON df.Dealfaultbillid=dfc.dealfaultbillid AND dfc.containername LIKE cm.productname||'/%'||cm.productbatchno||'%'               
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strsql)
        If para.ContainsKey("DealFaultBillId") Then
            sb.AppendFormat(" where df.DealFaultBillId='{0}'", para("DealFaultBillId"))
        End If

        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' Add by Yuxj20180820
    ''' 根据批次条件，查找系统满足的批次
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function SearchContainer(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDealFaultBillIbusiness.SearchContainer
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT DISTINCT cc.containername FROM containercurrentinfo cc ")
        sb.AppendFormat(" where cc.factoryid='{0}' and cc.productname='{1}' and cc.containername like '%{2}%' ",
                        para("FactoryId"), para("PName"), para("BatchNo"))
        If para.ContainsKey("CName") Then
            sb.AppendFormat(" and cc.containerName like '%{0}%'", para("CName"))
        End If
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' Add by Yuxj20180820
    ''' 记录批次号
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function InsertDealFaultContainer(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSDealFaultBillIbusiness.InsertDealFaultContainer
        Dim sb As New StringBuilder
        InsertDealFaultContainer = False
        sb.AppendLine(" insert into DealFaultBillContainer(containerid,dealfaultbillid) values ")
        sb.AppendFormat(" ('{0}','{1}'  ) ", para("ContainerId"), para("DealFaultBillId"))
        DatabaseHelper.ExecuteSql(sb.ToString)
        InsertDealFaultContainer = True
    End Function

    ''' <summary>
    ''' Add by Yuxj20180820
    ''' 删除批次号
    ''' </summary>
    ''' <param name="DealFaultBillId"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function DeleteDealFaultContainer(ByVal DealFaultBillId As String) As Boolean Implements uLMSDealFaultBillIbusiness.DeleteDealFaultContainer
        DeleteDealFaultContainer = False
        Dim strsql As String = String.Format(" delete from dealfaultbillContainer  where DealFaultBillId='{0}' ", DealFaultBillId)
        DatabaseHelper.ExecuteSql(strsql)
        DeleteDealFaultContainer = True
    End Function

    ''' <summary>
    ''' Add by Yuxj20181106
    ''' </summary>
    ''' <param name="REWORKREASONID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetReworkReasonData(ByVal REWORKREASONID As String) As DataTable Implements uLMSDealFaultBillIbusiness.GetReworkReasonData
        Dim strsql As String = String.Format(" select * from REWORKREASON r where r.REWORKREASONID='{0}' ", REWORKREASONID)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        Return dt
    End Function
#End Region

#Region "新排故功能"
    '排故申请
    ''' <summary>
    ''' 装试排故新申请yuxj20190610
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function SaveDealFaultBill(ByVal entity As BusinessRule.DTO.dealFaultBillInfoDTO, ByVal SaveType As Integer) As Boolean Implements uLMSDealFaultBillIbusiness.SaveDealFaultBill
        SaveDealFaultBill = False
        Dim strsql As String = String.Empty
        If SaveType = 1 Then
            strsql = <string>
                          update DealFaultBILL set Productname=:Productname,Productbatchno=:Productbatchno,DealFaultTYPE=:DealFaultTYPE,
                        FAULTDESC=:FAULTDESC,MACHINEWORKHOURS=:MACHINEWORKHOURS,RETURNFACTORYID=:RETURNFACTORYID,
                       ENGINEFAMILY=:ENGINEFAMILY,ENGINENO=:ENGINENO,PRODUCTANDBATCH=:PRODUCTANDBATCH,DEALFAULTSTATUS=:DEALFAULTSTATUS,IsFaultCheck=:IsFaultCheck 
                        where DealFaultBILLNAME=:DealFaultBILLNAME
                      </string>
            Dim insertpar(11) As OracleParameter
            insertpar(0) = DatabaseHelper.NewParameter(":Productname", entity.ProductName, OracleType.VarChar)
            insertpar(1) = DatabaseHelper.NewParameter(":Productbatchno", entity.Productbatchno, OracleType.VarChar)
            insertpar(2) = DatabaseHelper.NewParameter(":DealFaultTYPE", entity.DealFaultTYPE, OracleType.VarChar)
            insertpar(3) = DatabaseHelper.NewParameter(":FAULTDESC", entity.FAULTDESC, OracleType.VarChar)
            insertpar(4) = DatabaseHelper.NewParameter(":MACHINEWORKHOURS", entity.MACHINEWORKHOURS, OracleType.VarChar)
            insertpar(5) = DatabaseHelper.NewParameter(":RETURNFACTORYID", entity.RETURNFACTORYID, OracleType.VarChar)
            insertpar(6) = DatabaseHelper.NewParameter(":ENGINEFAMILY", entity.EngineFamily, OracleType.VarChar)
            insertpar(7) = DatabaseHelper.NewParameter(":ENGINENO", entity.EngineNo, OracleType.VarChar)
            insertpar(8) = DatabaseHelper.NewParameter(":PRODUCTANDBATCH", entity.ProductAndBatch, OracleType.VarChar)
            insertpar(9) = DatabaseHelper.NewParameter(":DEALFAULTSTATUS", entity.DEALFAULTSTATUS, OracleType.VarChar)
            insertpar(10) = DatabaseHelper.NewParameter(":DealFaultBILLNAME", entity.DealFaultBILLNAME, OracleType.VarChar)
            insertpar(11) = DatabaseHelper.NewParameter(":IsFaultCheck", entity.IsFaultCheck, OracleType.VarChar) 'add by longb 2023-08-16 是否故检
            Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql, insertpar)
            If rows = 1 Then
                Return True
            Else
                Return False
            End If
        Else
            strsql =
            <string>
                INSERT INTO DealFaultBILL
                      (Productname,Productbatchno,CREATEDATE,CREATEEMPLOYEEID,   DealFaultBILLID,   DealFaultBILLNAME,
                       DealFaultTYPE,   FAULTDESC,   MACHINEWORKHOURS,   REPAIRCOUNT,   RETURNFACTORYID,
                       transferstatus, istransfer, isuse,ENGINEFAMILY,ENGINENO,mfgorderid,DEALFAULTSTATUS,isreturn,PRODUCTANDBATCH,IsFaultCheck)
            VALUES
                      (:Productname,   :Productbatchno,   SYSDATE,   :CREATEEMPLOYEEID,   :DealFaultBILLID,   :DealFaultBILLNAME,
                       :DealFaultTYPE,   :FAULTDESC,   :MACHINEWORKHOURS,   0,   :RETURNFACTORYID,:transferstatus,'0','0',
                       :ENGINEFAMILY,:ENGINENO,:mfgorderid,:DEALFAULTSTATUS,:isreturn,:PRODUCTANDBATCH,:IsFaultCheck)
            </string>
            Dim insertpar(16) As OracleParameter
            insertpar(0) = DatabaseHelper.NewParameter(":Productname", entity.ProductName, OracleType.VarChar)
            insertpar(1) = DatabaseHelper.NewParameter(":Productbatchno", entity.Productbatchno, OracleType.VarChar)
            insertpar(2) = DatabaseHelper.NewParameter(":CREATEEMPLOYEEID", entity.CREATEEMPLOYEEID, OracleType.VarChar)
            insertpar(3) = DatabaseHelper.NewParameter(":DealFaultBILLID", entity.DealFaultBILLID, OracleType.VarChar)
            insertpar(4) = DatabaseHelper.NewParameter(":DealFaultBILLNAME", entity.DealFaultBILLNAME, OracleType.VarChar)
            insertpar(5) = DatabaseHelper.NewParameter(":DealFaultTYPE", entity.DealFaultTYPE, OracleType.VarChar)
            insertpar(6) = DatabaseHelper.NewParameter(":FAULTDESC", entity.FAULTDESC, OracleType.VarChar)
            insertpar(7) = DatabaseHelper.NewParameter(":MACHINEWORKHOURS", entity.MACHINEWORKHOURS, OracleType.VarChar)
            insertpar(8) = DatabaseHelper.NewParameter(":RETURNFACTORYID", entity.RETURNFACTORYID, OracleType.VarChar)
            insertpar(9) = DatabaseHelper.NewParameter(":ENGINEFAMILY", entity.EngineFamily, OracleType.VarChar)
            insertpar(10) = DatabaseHelper.NewParameter(":ENGINENO", entity.EngineNo, OracleType.VarChar)
            insertpar(11) = DatabaseHelper.NewParameter(":mfgorderid", entity.MFGORDERID, OracleType.VarChar)
            insertpar(12) = DatabaseHelper.NewParameter(":DEALFAULTSTATUS", entity.DEALFAULTSTATUS, OracleType.VarChar)
            insertpar(13) = DatabaseHelper.NewParameter(":isreturn", entity.ISRETURN, OracleType.VarChar)
            insertpar(14) = DatabaseHelper.NewParameter(":transferstatus", entity.TransferStatus, OracleType.VarChar)
            insertpar(15) = DatabaseHelper.NewParameter(":PRODUCTANDBATCH", entity.ProductAndBatch, OracleType.VarChar)
            insertpar(16) = DatabaseHelper.NewParameter(":IsFaultCheck", entity.IsFaultCheck, OracleType.VarChar) 'add by longb 2023-08-16 是否故检
            Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql, insertpar)
            If (rows = 0) Then
                Return False
            Else
                strsql = String.Format(" insert into DEALFAULTBILLTRANSFER(DealFaultBILLID) values('{0}') ", entity.DealFaultBILLID)
                DatabaseHelper.ExecuteSql(strsql)
                Return True
            End If
        End If
    End Function
    ''' <summary>
    ''' '排故审核和确认
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function UpdateDealFaultTransfer(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSDealFaultBillIbusiness.UpdateDealFaultTransfer
        UpdateDealFaultTransfer = False
        Dim sb As New StringBuilder
        sb.AppendLine("update  DEALFAULTBILLTRANSFER dft set ")
        If para.ContainsKey("Type") Then
            If para("Type") = "1" Then
                sb.AppendFormat(" dft.audittime=SYSDATE,dft.auditempid='{0}' ", para("EmployeeID"))
            ElseIf para("Type") = "2" Then
                sb.AppendFormat(" dft.confirmtime=SYSDATE,dft.confirmempid='{0}' ", para("EmployeeID"))
            ElseIf para("Type") = "3" Then '返出
                sb.AppendFormat(" dft.fromtime=sysdate, dft.fromempid='{0}' ", para("EmployeeID"))
                sb.AppendFormat(" ,dft.pagecount='{0}',dft.fromqty='{1}'", para("SendPageCount"), para("SendQty"))
                sb.AppendFormat(" ,dft.requirefinishtime=to_date('{0}','yyyy/mm/dd hh24:mi:ss')", para("RequireCompleteDate"))
            ElseIf para("Type") = "4" Then '接收
                Dim vendorid As String = ""
                If para.ContainsKey("vendorid") Then
                    vendorid = para("vendorid")
                End If
                sb.AppendFormat(" dft.recvfactoryempid='{0}',dft.RevRejectReason=null,dft.recvfactorytime=sysdate,dft.ESTIMATEDCOMPLETEDATE=to_date('{1}','yyyy/mm/dd'),vendorid='{2}' ",
                                para("EmployeeID"), para("EstimatedCompleteDate"), vendorid) 'add:Wangjh 20190711 增加预计完成时间
            ElseIf para("Type") = "5" Then '完成 modify by longb 2023-07-07 新增判断是否要故检流程
                If para("isfaultcheck") = "否" Then
                    sb.AppendFormat(" dft.finishempid='{0}',dft.finishtime=sysdate,dft.finishscrapqty='{1}',dft.pagecountfinish='{2}' ",
                                    para("EmployeeID"), para("ScrapQty"), para("FinishPageCount"))
                Else
                    sb.AppendFormat(" dft.faultcheckempid='{0}',faultcheckdate=sysdate,entitystoragelocation='{1}',dft.finishscrapqty='{2}',dft.pagecountfinish='{3}',faultcheckreportno=null,dealfileno=null ", para("EmployeeID"), para("entitystoragelocation"), para("ScrapQty"), para("FinishPageCount"))
                End If
            ElseIf para("Type") = "6" Then '返回
                sb.AppendFormat(" dft.recvfactoryreturnempid='{0}',dft.RevReturnRejectReason=null, dft.recvfactoryreturntime=sysdate ", para("EmployeeID"))
            ElseIf para("Type") = "7" Then '领取 
                If para.ContainsKey("Reject") Then 'modify by longb 不是领取【退回】操作视为【确认】
                    sb.AppendFormat(" dft.recvfactoryreturnempid=null,dft.recvfactoryreturntime=null ")
                Else
                    sb.AppendFormat(" dft.acceptteamid='{0}', dft.accepttime=sysdate,dft.RecReturnRejectReason=null ", para("EmployeeID"))
                End If
            ElseIf para("Type") = "8" Then '返件质检确认 add by longb 2023-07-07
                sb.AppendFormat(" dft.faultcheckreportnoempid='{0}',faultcheckreportnodate=sysdate,faultcheckreportno='{1}'", para("EmployeeID"), para("faultcheckreportno"))
            ElseIf para("Type") = "9" Then '返件技术确认 add by longb 2023-07-07
                sb.AppendFormat(" dft.finishempid='{0}',dealfileno='{1}',dft.finishtime=sysdate", para("EmployeeID"), para("dealfileno"))
            End If
        End If
        sb.AppendFormat(" where  dft.dealfaultbillid IN ('{0}')", para("DealFaultBillID").Replace(",", "','"))
        DatabaseHelper.ExecuteSql(sb.ToString)

        If para.ContainsKey("Reject") Then
            sb = New StringBuilder
            sb.AppendLine("update  DEALFAULTBILLTRANSFER dft set ")
            If para("Type") = "4" Then '返件接收 modify by longb 2023-07-12 接收【退回】新增字段调整
                sb.AppendFormat(" dft.RevRejectReason='{0}'||' 退回人:'||(SELECT e.FULLNAME FROM EMPLOYEE e WHERE e.EMPLOYEEID='{1}')||' 退回时间:'||to_char(sysdate,'YYYY-MM-dd hh24:mi:ss')||' 要求完成时间：'||to_char(RequireFinishTime,'YYYY-MM-dd hh24:mi:ss'),revrejectempid='{1}',revrejectdate=sysdate,dft.recvfactoryempid=null,recvfactorytime=null,RequireFinishTime=null", para("RevRejectReason"), para("EmployeeID"))
            ElseIf para("Type") = "6" Then '返件返回【退回】操作--
                If String.IsNullOrWhiteSpace(para("entitystoragelocation")) Then '为空：不涉及故检流程
                    sb.AppendFormat(" dft.RevReturnRejectReason='{0}'||' 确认人:'||(SELECT e.FULLNAME FROM EMPLOYEE e WHERE e.EMPLOYEEID=dft.FINISHEMPID)||' 完成时间:'|| to_char(dft.FINISHTIME,'yyyy-MM-dd hh24:mi:ss')||' 退回人:'||(SELECT e.FULLNAME FROM EMPLOYEE e WHERE e.EMPLOYEEID='{1}')||' 退回时间:'||to_char(sysdate,'YYYY-MM-dd hh24:mi:ss'),dft.FINISHEMPID =NULL,dft.FINISHTIME =null,dft.recvfactoryreturntime=null", para("RevReturnRejectReason"), para("EmployeeID"))
                Else '涉及故检流程
                    Dim sql As String = <string>
                                            dft.RevReturnRejectReason='{0}'||' 退回人:'||(SELECT e.FULLNAME FROM EMPLOYEE e WHERE e.EMPLOYEEID='{1}')||' 退回时间:'||to_char(sysdate,'YYYY-MM-dd hh24:mi:ss')
                                        ||' 实物存放地:'||dft.ENTITYSTORAGELOCATION ||' 故检提交人:'||(SELECT e.FULLNAME FROM EMPLOYEE e WHERE e.EMPLOYEEID=dft.FAULTCHECKEMPID)||' 故检提交时间:'|| to_char(dft.FAULTCHECKDATE,'yyyy-MM-dd hh24:mi:ss')
                                        ||' 故检报告号:'||dft.FAULTCHECKREPORTNO||' 质检确认人:'||(SELECT e.FULLNAME FROM EMPLOYEE e WHERE e.EMPLOYEEID=dft.FAULTCHECKREPORTNOEMPID)||' 质检确认时间:'||to_char(dft.FAULTCHECKREPORTNODATE,'yyyy-MM-dd hh24:mi:ss')
                                        ||'	修理文件号:'||dft.DEALFILENO||' 技术确认人:'||(SELECT e.FULLNAME FROM EMPLOYEE e WHERE e.EMPLOYEEID=dft.FINISHEMPID)||' 技术确认完成时间:'|| to_char(dft.FINISHTIME,'yyyy-MM-dd hh24:mi:ss')
                                        ||' 退回人:'||(SELECT e.FULLNAME FROM EMPLOYEE e WHERE e.EMPLOYEEID='{1}')||' 退回时间:'||to_char(sysdate,'YYYY-MM-dd hh24:mi:ss')
                                            ,dft.FINISHEMPID =NULL,dft.FINISHTIME =null,dft.ENTITYSTORAGELOCATION=NULL,dft.FAULTCHECKEMPID=NULL,
                                        dft.FAULTCHECKDATE=NULL,dft.FAULTCHECKREPORTNO=NULL,dft.FAULTCHECKREPORTNOEMPID=NULL,dft.FAULTCHECKREPORTNODATE=null,dft.DEALFILENO=null,dft.recvfactoryreturntime=null
                                        </string>
                    sb.AppendFormat(sql, para("RevReturnRejectReason"), para("EmployeeID"))
                End If
            ElseIf para("Type") = "7" Then '返件领取【退回】操作--实物未到分厂 add by longb 2023-08-16
                sb.AppendFormat(" dft.RecReturnRejectReason='{0}'", para("RecReturnRejectReason"))
            End If

            sb.AppendFormat(" where  dft.dealfaultbillid IN ('{0}')", para("DealFaultBillID").Replace(",", "','"))
            DatabaseHelper.ExecuteSql(sb.ToString)
        End If

        If para.ContainsKey("Type") Then
            sb = New StringBuilder
            If para.ContainsKey("Reject") Then
                '页面参数4,6，未确认的为3,5  需要处理后为2,4
                sb.AppendFormat(" update DealFaultBILL set Transferstatus='{0}' ", (Convert.ToInt32(para("Type")) - 2).ToString)
            ElseIf para("Type") = "5" Then 'add by longb 2023-07-07 判断是否涉及故检
                sb.AppendFormat(" update DealFaultBILL set Transferstatus='{0}',isfaultcheck='{1}' ", IIf(para("isfaultcheck") = "否", para("Type"), "8"), IIf(para("isfaultcheck") = "否", 0, 1))
            ElseIf para("Type") = "9" Then 'add by longb 2023-07-07 返件技术确认--完成--状态改为5 正常返件完成
                sb.AppendFormat(" update DealFaultBILL set Transferstatus='{0}' ", "5")
            Else
                sb.AppendFormat(" update DealFaultBILL set Transferstatus='{0}' ", para("Type"))
            End If
            sb.AppendFormat(" where  dealfaultbillid IN ('{0}')", para("DealFaultBillID").Replace(",", "','"))
        End If
        DatabaseHelper.ExecuteSql(sb.ToString)
        UpdateDealFaultTransfer = True
    End Function

    ''' <summary>
    ''' 获取排故单信息
    ''' </summary>
    ''' <param name="dfname"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetDFinfo(ByVal dfname As String) As DataTable Implements uLMSDealFaultBillIbusiness.GetDFinfo
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT df.Productname,df.Productbatchno,df.Machineworkhours, Dealfaultstatus,")
        sb.AppendLine("df.Returnfactoryid,df.Dealfaulttype,df.Dealfaultbillname,df.Faultdesc,f.factoryid,f.factoryname,df.productandbatch,decode(df.isfaultcheck,'0','否','1','是',df.isfaultcheck) isfaultcheck")
        sb.AppendFormat(" FROM dealfaultbill DF LEFT JOIN FACTORY f ON df.Returnfactoryid=f.factoryid ")
        sb.AppendFormat(" where df.Dealfaultbillname='{0}'", dfname)
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 删除排故单
    ''' </summary>
    ''' <param name="strDealFaultBill"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function DeleteDealFaultBill(ByVal strDealFaultBill As String) As Boolean Implements uLMSDealFaultBillIbusiness.DeleteDealFaultBill
        Dim sb As New StringBuilder
        sb.AppendFormat("DELETE DEALFAULTBILLTRANSFER where DealFaultBILLID IN （ ")
        sb.AppendFormat(" SELECT DealFaultBILLID FROM DealFaultBILL where DealFaultBILLNAME='{0}')", strDealFaultBill)
        DatabaseHelper.ExecuteSql(sb.ToString)
        sb = New StringBuilder
        'sb.AppendFormat("DELETE  DealFaultBILL where DealFaultBILLNAME='{0}'", strDealFaultBill)
        'Modify Duht 逻辑删除 20201026
        sb.AppendFormat("update  DealFaultBILL set ISUSE=1 where DealFaultBILLNAME='{0}'", strDealFaultBill)
        DatabaseHelper.ExecuteSql(sb.ToString)
        Return True

    End Function
    ''' <summary>
    ''' 作废排故单 add by longb 2022-04-24
    ''' </summary>
    ''' <param name="strDealFaultBill"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function CanCleDealFaultBill(ByVal strDealFaultBill As String) As Boolean Implements uLMSDealFaultBillIbusiness.CancleDealFaultBill
        Dim sb As New StringBuilder
        sb.AppendFormat("update  DealFaultBILL set ISUSE=2 where DEALFAULTBILLID='{0}'", strDealFaultBill)
        DatabaseHelper.ExecuteSql(sb.ToString)
        sb = New StringBuilder
        sb.AppendFormat("update  DEALFAULTBILLTRANSFER set ISUSE=2 where DEALFAULTBILLID='{0}'", strDealFaultBill)
        DatabaseHelper.ExecuteSql(sb.ToString)
        Return True

    End Function

    ''' <summary>
    ''' 返件返回入集件清单
    ''' add by longb 2022-06-28
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function InsertCollectErpmaterial(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSDealFaultBillIbusiness.InsertCollectErpmaterial
        Dim result = False
        Dim sb As New StringBuilder
        sb.AppendFormat("SELECT * FROM collecterpmaterial WHERE MFGORDERNAME='{0}' AND PRODUCTBATCHNO='{1}' AND PRODUCTNAME='{2}'", para("MfgOrderName"), para("ProductBatchNo"), para("ProductName"))
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        If dt.Rows.Count = 0 Then
            sb = New StringBuilder
            sb.AppendLine("INSERT INTO COLLECTERPMATERIAL(COLLECTERPMATERIALID, CREATEDATE, MFGORDERNAME, PRODUCTBATCHNO, PRODUCTNAME, QTY, UOM, ORIGINALPRODUCTNAME, ISERP)")
            sb.AppendFormat("VALUES('{0}',sysdate, '{1}', '{2}', '{3}', '{4}', '件','{3}', '0')", para("CollecterpmaterialID"), para("MfgOrderName"), para("ProductBatchNo"), para("ProductName"), para("Qty"))
            result = DatabaseHelper.ExecuteSql(sb.ToString) > 0
        End If
        Return result
    End Function


    ''' <summary>
    ''' 获取开具排故单订单信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetPGMfgorderinfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSDealFaultBillIbusiness.GetPGMfgorderinfo
        Dim strsql As String =
            <string>
                SELECT m.mfgordername,m.mfgorderid,ot.ordertypename,u.attributevalue,pb.productname,p.description,m.productfamilyname,
                NVL2(ci.aufnr,'已入库',NVL2(ep.mfgorderid,'已分解','未分解')) status,
                DECODE(db.dfcount,0,NULL,db.dfcount) dfcount FROM mfgorder m
                LEFT JOIN Ordertype ot ON m.ordertypeid=ot.ordertypeid
                LEFT JOIN Userattribute u ON m.mfgorderid=u.parentid AND u.userattributename='发动机'
                LEFT JOIN product p1 ON p1.productid=m.productid
                LEFT JOIN productbase pb ON nvl(p1.productbaseid,m.productbaseid)=pb.productbaseid
                LEFT JOIN product p ON  p.productid=pb.revofrcdid
                --LEFT JOIN (select pw.productid,max(pw.workflowid) workflowid from productworkflowmapping pw group by pw.productid) pw on pw.productid=p.productid
                --LEFT JOIN (SELECT wff.workflowid,max(wff.productfamilylistid) productfamilylistid FROM  Workflowproductfamilylist wff GROUP BY wff.workflowid) wff 
                --          ON pw.workflowid=wff.workflowid 
                --LEFT JOIN productfamily pf ON wff.productfamilylistid=pf.productfamilyid
                LEFT JOIN containerinstore ci ON ci.aufnr=m.mfgordername
                LEFT JOIN (SELECT distinct ep.mfgorderid FROM enginepartplan ep) ep ON ep.mfgorderid=m.mfgorderid
                LEFT JOIN (SELECT db.mfgorderid,COUNT(1) dfcount FROM dealfaultbill db where db.isuse=0
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strsql)
        If para.ContainsKey("IsReturn") Then
            sb.AppendFormat(" and db.isreturn='{0}' ", para("IsReturn"))
        End If
        sb.AppendLine(" GROUP BY db.mfgorderid) db ON m.mfgorderid=db.mfgorderid ")
        sb.AppendFormat("where m.factoryid='{0}' ", para("FactoryID"))
        Dim zpsb As New StringBuilder 'add by longb 2023-11-07 
        '如果是试车厂，加上-筛选2023-09-01之前的装配厂（原装配试车厂）的数据 add by longb 2023-11-07
        If para("FactoryID").Equals("0004e280000039ab") Then
            zpsb.AppendLine(" OR (m.factoryid='0004e280000026e5' AND to_char(m.plannedcompletiondate,'yyyy-MM-dd')<'2023-09-01' ")
        End If

        If para.ContainsKey("MfgOrderName") Then
            sb.AppendFormat("and m.mfgordername like '%{0}%' ", para("MfgOrderName"))
            If para("FactoryID").Equals("0004e280000039ab") Then ' add by longb 2023-11-07
                zpsb.AppendFormat("and m.mfgordername like '%{0}%' ", para("MfgOrderName"))
            End If
        End If

        If para.ContainsKey("Status") Then
            If para("Status") = "已入库" Then
                sb.AppendFormat("and ci.aufnr is not null ")
                If para("FactoryID").Equals("0004e280000039ab") Then ' add by longb 2023-11-07
                    zpsb.AppendFormat("and ci.aufnr is not null ")
                End If
            ElseIf para("Status") = "未分解" Then
                sb.AppendLine(" and  ep.mfgorderid is null ")
                If para("FactoryID").Equals("0004e280000039ab") Then ' add by longb 2023-11-07
                    zpsb.AppendLine(" and  ep.mfgorderid is null ")
                End If
            Else '已分解未入库
                sb.AppendFormat(" and ep.mfgorderid is not null and ci.aufnr is null ")
                If para("FactoryID").Equals("0004e280000039ab") Then ' add by longb 2023-11-07
                    zpsb.AppendFormat(" and ep.mfgorderid is not null and ci.aufnr is null ")
                End If
            End If
        End If

        If para.ContainsKey("Machine") Then
            sb.AppendFormat("and u.attributevalue like '%{0}%' ", para("Machine"))
            If para("FactoryID").Equals("0004e280000039ab") Then ' add by longb 2023-11-07
                zpsb.AppendFormat("and u.attributevalue like '%{0}%' ", para("Machine"))
            End If
        End If
        If para("FactoryID").Equals("0004e280000039ab") Then ' add by longb 2023-11-07
            sb.AppendLine(zpsb.ToString() + ")")
        End If
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        GetPGMfgorderinfo = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 根据订单Id获取排故单
    ''' </summary>
    ''' <param name="mfgid"></param>
    ''' <param name="type"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetPGDbyMfgId(ByVal mfgid As String, ByVal type As String) As DataTable Implements uLMSDealFaultBillIbusiness.GetPGDbyMfgId
        Dim strsql =
            String.Format("SELECT df.Productbatchno,df.Productname,df.Dealfaultbillname ")
        strsql += String.Format(",CASE WHEN DF.TRANSFERSTATUS='8' THEN DECODE(DFT.faultcheckreportno,NULL,'待检验故检报告','待工程部修理文件') else ") 'add by longb 2023-07-25
        strsql += String.Format("DECODE(DF.TRANSFERSTATUS,  '0','待返出单位技术审核',   '1','待返出单位检验确认',  '2', '待返出单位返出', '3', '待接收单位接收',  '4', ")
        strsql += "'待接收单位完成', '5', DECODE(DFT.faultcheckreportno,NULL,'待接收单位返回','待接收单位修理'),'6', '待返出单位领取', '7', '现场已领取',DF.TRANSFERSTATUS) END STATUS,df.TRANSFERSTATUS "
        strsql += String.Format(" FROM dealfaultbill DF LEFT JOIN DEALFAULTBILLTRANSFER DFT ON DFT.DEALFAULTBILLID =DF.DEALFAULTBILLID WHERE df.Mfgorderid='{0}' ", mfgid)

        strsql += String.Format(" and df.isreturn='{0}' and df.isuse=0 ", type)
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function

    ''' <summary>
    ''' 获取发动机装配信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetDFIssueMaterial(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDealFaultBillIbusiness.GetDFIssueMaterial
        Dim sb As New StringBuilder
        Dim strsql As String =
            <string>
                SELECT cm.productbatchno,pb.productname,p.description, im.issueqty,cm.mfgordername FROM issueerpmaterial im 
                LEFT JOIN enginepartplan ep ON im.containerid=NVL(ep.containerid,ep.enginecontainerid)
                LEFT JOIN Collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid
                LEFT JOIN partmenuinfo pm ON pm.partmenuinfoid=im.partmenuinfoid
                LEFT JOIN product p ON pm.productid=p.productid    
                LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid 
            </string>
        sb.AppendLine(strsql)
        sb.AppendFormat("WHERE ep.mfgorderid='{0}' ", para("MfgOrderId"))
        If para.ContainsKey("PDesc") Then
            sb.AppendFormat("and p.description like '%{0}%'", para("PDesc"))
        End If

        If para.ContainsKey("PName") Then
            sb.AppendFormat("and pb.productname like '%{0}%'", para("PName"))
        End If
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function

    ''' <summary>
    ''' 获取排故单查询方法
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetPGDataInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSDealFaultBillIbusiness.GetPGDataInfo
        Dim strsql As String = GetPGDSql(para)
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strsql
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    Function GetPGDataTable(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSDealFaultBillIbusiness.GetPGDataTable
        Dim strsql As String = GetPGDSql(para)
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function

    Private Function GetPGDSql(ByVal para As Dictionary(Of String, String)) As String
        Dim sb As New StringBuilder
        Dim strsql As String =
            <string>
                 SELECT M.MFGORDERID,
                       M.MFGORDERNAME,
                       DF.ENGINENO,
                       OT.ORDERTYPENAME,
                       P.DESCRIPTION,
                       DF.DEALFAULTBILLNAME,
                       DF.ENGINEFAMILY,
                       CASE WHEN DF.TRANSFERSTATUS='8' THEN DECODE(DFT.faultcheckreportno,NULL,'待检验故检报告','待工程部修理文件') else 
                    DECODE(DF.TRANSFERSTATUS,  '0','待返出单位技术审核',   '1','待返出单位检验确认',  '2', '待返出单位返出', '3', '待接收单位接收',  '4', 
                    '待接收单位完成', '5', DECODE(DFT.faultcheckreportno,NULL,'待接收单位返回','待接收单位修理'),'6', '待返出单位领取', '7', '现场已领取',DF.TRANSFERSTATUS) END TRANSFERSTATUS,
                       DF.DEALFAULTTYPE,
                       DF.PRODUCTNAME,
                       DF.PRODUCTBATCHNO, DF.PRODUCTANDBATCH,                      
                       FF.FACTORYNAME,
                       DFT.REQUIREFINISHTIME,
                       DFT.PAGECOUNTFINISH,
                       DFT.PAGECOUNT,
                       DFT.FROMQTY,
                       DFT.REVREJECTREASON,
                       DFT.REVRETURNREJECTREASON,
                       DFT.FINISHSCRAPQTY,
                       DF.MACHINEWORKHOURS,F.FACTORYNAME AcceptFACTORYNAME,decode(DF.ISARMY,1,'是','否') ISARMY,
                       DF.AUDITDATE,decode(DF.AUDITEMPLOYEENAME,null,'','/InSiteWebApplication/JDBimages/'||DF.AUDITEMPLOYEENAME||'.png') AUDITEMPLOYEENAME,                     
                       DF.RESULTAUDITDATE,decode(DF.RESULTAUDITEMPLOYEENAME,null,'','/InSiteWebApplication/JDBimages/'||DF.RESULTAUDITEMPLOYEENAME||'.png') RESULTAUDITEMPLOYEENAME, 
                       DF.DEALFAULTBILLID, DF.NOTES,DF.FAULTDESC,df.Dealfaultstatus,
                       GETONLYFULLNAME(DF.CREATEEMPLOYEEID) CREATEEMP,DF.CREATEDATE,
                       decode(DFT.recvfactoryreturntime,null,null,to_char(DFT.recvfactoryreturntime,'yyyy/mm/dd')) returntime
                       ,GETONLYFULLNAME(DFT.AUDITEMPID) AUDITEMP,DFT.AUDITTIME
                       ,GETONLYFULLNAME(DFT.CONFIRMEMPID) CONFIRMEMP,DFT.CONFIRMTIME
                       ,GETONLYFULLNAME(DFT.fromempid) fromemp,DFT.fromtime
                       ,GETONLYFULLNAME(DFT.recvfactoryempid) recvfactoryemp,DFT.recvfactorytime                       
                       ,GETONLYFULLNAME(DFT.finishempid ) finishemp,DFT.finishtime
                       ,GETONLYFULLNAME(DFT.recvfactoryreturnempid) recvfactoryreturnemp,DFT.recvfactoryreturntime
                       ,GETONLYFULLNAME(DFT.acceptteamid) acceptteam,DFT.accepttime
                       ,GETONLYFULLNAME(DFT.faultcheckempid) faultcheckemp,DFT.faultcheckdate,DFT.entitystoragelocation--add by longb 2023-07-07
                       ,GETONLYFULLNAME(DFT.faultcheckreportnoempid) faultcheckreportnoemp,DFT.faultcheckreportnodate,DFT.faultcheckreportno--add by longb 2023-07-07
                       ,DFT.dealfileno,DF.ISFAULTCHECK,DFT.RECRETURNREJECTREASON,decode(DF.ISFAULTCHECK,'1','是','否') ISFAULTCHECKT--add by longb 2023-07-07
                       ,TRUNC(DFT.faultcheckreportnodate)-TRUNC(DFT.faultcheckdate) faultcheckreportday--质检确认时间-返件完成时间
                       ,TRUNC(DFT.finishtime)-TRUNC(DFT.faultcheckreportnodate) dealfileday--技术确认时间-返件质检时间
                       ,TRUNC(DFT.recvfactoryreturntime)-TRUNC(DFT.finishtime) dealday--返件返回时间-技术确认时间
                       ,item.customobjectitemsname,item.customobjectitemsdesc
            </string>
        sb.AppendLine(strsql)
        'sb.AppendLine(",GETONLYFULLNAME(DFT.CONFIRMEMPID) CONFIRMEMP,DFT.CONFIRMTIME ")
        'If para.ContainsKey("Status") Then
        '    If para("Type") = "1" Then
        '        sb.AppendLine(" ,GETONLYFULLNAME(DFT.AUDITEMPID) AUDITEMP,DFT.AUDITTIME ")
        '    ElseIf para("Type") = "2" Then
        '        sb.AppendLine(" ,GETONLYFULLNAME(DFT.AUDITEMPID) AUDITEMP,DFT.AUDITTIME ")
        '        'sb.AppendLine(",GETONLYFULLNAME(DFT.CONFIRMEMPID) CONFIRMEMP,DFT.CONFIRMTIME ")
        '    ElseIf para("Type") = "3" Then
        '        sb.AppendLine(",GETONLYFULLNAME(DFT.fromempid) fromemp,DFT.fromtime ")
        '    ElseIf para("Type") = "4" Then
        '        sb.AppendLine(",GETONLYFULLNAME(DFT.recvfactoryempid) recvfactoryemp,DFT.recvfactorytime ")
        '    ElseIf para("Type") = "5" Then
        '        sb.AppendLine(",GETONLYFULLNAME(DFT.finishempid ) finishemp,DFT.finishtime ")
        '    ElseIf para("Type") = "6" Then
        '        sb.AppendLine(",GETONLYFULLNAME(DFT.recvfactoryreturnempid) recvfactoryreturnemp,DFT.recvfactoryreturntime ")
        '    ElseIf para("Type") = "7" Then
        '        sb.AppendLine(",GETONLYFULLNAME(DFT.acceptteamid) acceptteam,DFT.accepttime ")
        '    End If
        'End If
        If para.ContainsKey("Type") Then
            If para("Type") = "7" Then
                sb.AppendLine(",nvl(DFT.fromqty,0)-NVL(DFT.finishscrapqty,0) qty ")
            End If
        End If

        strsql =
            <string>                  
                  FROM DEALFAULTBILL DF
                  LEFT JOIN MFGORDER M
                    ON DF.MFGORDERID = M.MFGORDERID
                   LEFT JOIN PRODUCT P
                    ON P.PRODUCTID = GET_SPLIT_MFGORDER_PRODUCTID(M.MFGORDERID)
                   LEFT JOIN ORDERTYPE OT
                    ON M.ORDERTYPEID = OT.ORDERTYPEID
                  LEFT JOIN DEALFAULTBILLTRANSFER DFT
                    ON DF.DEALFAULTBILLID = DFT.DEALFAULTBILLID
                  LEFT JOIN FACTORY F
                    ON F.FACTORYID = DF.RETURNFACTORYID 
                  LEFT JOIN EMPLOYEE E
                                        ON DF.CREATEEMPLOYEEID =
                                           E.EMPLOYEEID
                  LEFT JOIN SESSIONVALUES SV
                                        ON E.SESSIONVALUESID =
                                           SV.SESSIONVALUESID
                   LEFT JOIN FACTORY FF
                                        ON FF.FACTORYID = SV.FACTORYID
                    LEFT JOIN customobjectitems item ON item.customobjectitemsid =DFT.vendorid
                  WHERE(DF.isuse = 0)
            </string>

        sb.AppendLine(strsql)
        If para.ContainsKey("MfgOrderName") Then
            sb.AppendFormat(" and m.MfgOrderName like '%{0}%' ", para("MfgOrderName"))
        End If

        If para.ContainsKey("PGD") Then
            sb.AppendFormat(" and df.DEALFAULTBILLNAME LIKE '%{0}%' ", para("PGD"))
        End If

        If para.ContainsKey("Machine") Then
            sb.AppendFormat(" and df.ENGINENO like '%{0}%' ", para("Machine"))
        End If
        sb.AppendFormat(" and df.Createdate >=to_date('{0}','yyyy/mm/dd') ", "2022/4/6") 'add:Wangjh 20220411 过滤到此日期之前的
        If para.ContainsKey("BeginDate") Then
            sb.AppendFormat(" and df.Createdate >=to_date('{0}','yyyy/mm/dd') ", para("BeginDate"))
        End If

        If para.ContainsKey("EndDate") Then
            sb.AppendFormat(" and df.Createdate <to_date('{0}','yyyy/mm/dd')+1 ", para("EndDate"))
        End If

        If para.ContainsKey("DealFaultBillID") Then
            sb.AppendFormat(" and df.DealFaultBillID = '{0}' ", para("DealFaultBillID"))
        End If

        If para.ContainsKey("Type") Then
            '只显示排故单，打印 审核 确认
            If para("Type") = "0" Or para("Type") = "1" Or para("Type") = "2" Then
                sb.AppendLine(" and df.isreturn='0' ")
            End If
        End If

        If para.ContainsKey("FactoryID") Then
            If para.ContainsKey("Type") Then
                If para("Type") = "4" Or para("Type") = "5" Or para("Type") = "6" Then
                    sb.AppendFormat(" and df.Returnfactoryid='{0}' ", para("FactoryID"))
                ElseIf para("Type") = "2" And para.ContainsKey("WzJyy") Then 'add by longb 2022-06-15 物资检验员查询返给物资的排故单
                    sb.AppendFormat(" and df.Returnfactoryid='{0}' ", para("FactoryID"))
                ElseIf para("Type") = "8" Then 'add by longb 2023-07-14 返件质检确认(检验检测中心才能查看)
                    sb.AppendFormat(" and EXISTS (SELECT 1 FROM USERATTRIBUTE u WHERE u.PARENTID ='{0}' AND u.USERATTRIBUTENAME ='分厂代码' AND u.ATTRIBUTEVALUE ='613')", para("FactoryID"))
                ElseIf para("Type") = "9" Then 'add by longb 2023-07-14 返件技术确认(工程技术部才能查看)
                    sb.AppendFormat(" and EXISTS (SELECT 1 FROM FActory f WHERE f.FACTORYID ='{0}' AND f.FACTORYNAME ='工程技术部')", para("FactoryID"))
                Else
                    sb.AppendFormat(" and FF.FACTORYID='{0}' ", para("FactoryID"))
                End If
            End If
        End If
        'add by longb 2022-06-15 判断当前登录人是否是【发动机总体事业部--装配检验员】用于过滤掉【物资部】创建的排故单
        If para.ContainsKey("ZpJyy") Then
            If para("ZpJyy") = "1" Then
                sb.AppendFormat(" AND f.FACTORYNAME NOT LIKE '%物资%' ")
            End If
        End If

        If para.ContainsKey("Status") Then
            If para("Status").IndexOf("确认") > -1 Then
                If para("Status").IndexOf("已") > -1 Then '未审核，未确认，未返出
                    If para("Type") = "1" Then
                        sb.AppendFormat(" and DF.TRANSFERSTATUS in ('1','2') ")
                    ElseIf para("Type") = "5" Then 'add by longb 2023-07-07 返件完成（已确认--取涉及故检+正常完成 两个状态）
                        sb.AppendFormat(" and DF.TRANSFERSTATUS in ('5','8') ")
                    ElseIf para("Type") = "8" Then 'add by longb 2023-07-07 返件质检确认（已确认）
                        sb.AppendFormat(" and DF.TRANSFERSTATUS ='{0}' and DFT.faultcheckreportno is not null ", para("Type"))
                    ElseIf para("Type") = "9" Then 'add by longb 2023-07-07 返件技术确认（已确认--取正常返件状态5）
                        sb.AppendFormat(" and DF.TRANSFERSTATUS in ('5') and DFT.dealfileno is not null")
                    Else
                        sb.AppendFormat(" and DF.TRANSFERSTATUS ='{0}' ", para("Type"))
                    End If
                Else
                    If para("Type") = "8" Then 'add by longb 2023-07-07返件质检确认（未确认：取状态8 && 故检报告号不为空） DFT.faultcheckreportno
                        sb.AppendFormat(" and DF.TRANSFERSTATUS ='{0}' and DFT.faultcheckreportno is null ", para("Type"))
                    ElseIf para("Type") = "9" Then
                        sb.AppendFormat(" and DF.TRANSFERSTATUS ='{0}' and  DFT.faultcheckreportno is not null ", (Convert.ToInt32(para("Type")) - 1).ToString)
                    Else
                        sb.AppendFormat(" and DF.TRANSFERSTATUS ='{0}' ", (Convert.ToInt32(para("Type")) - 1).ToString)
                        If para("Type").Equals("3") Then 'add by longb 2024-10-14 返件返出 新增【已退回】状态专门查看退回的数据
                            sb.AppendFormat(" and DFT.REVREJECTREASON is {0}", If(para("Status").IndexOf("退回") > -1, "not null", "null"))
                        End If
                    End If
                End If
            Else
                Dim type As String = String.Empty
                If para("Status") = "待返出" Then
                    type = "2"
                ElseIf para("Status") = "待接收" Then
                    type = "3"
                ElseIf para("Status") = "待完成" Then
                    type = "4"
                ElseIf para("Status") = "待返回" Then
                    type = "5"
                ElseIf para("Status") = "待领取" Then
                    type = "6"
                ElseIf para("Status") = "已领取" Then
                    type = "7"
                End If
                sb.AppendFormat(" and DF.TRANSFERSTATUS ='{0}' ", type)
            End If

        End If
        Return sb.ToString
    End Function
#End Region

End Class
